/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package boilerplates;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author daniel
 */
public class Database {
    Connection conn;
    public Database() throws SQLException, ClassNotFoundException{
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    }

    /**
     * Deletes all boilerplates and creates a fresh table. Use with caution.
     * @throws SQLException
     */
    public void recreateDatabase() throws SQLException{
        Statement stat = conn.createStatement();
        String query="CREATE TABLE Boilerplates (" +
          "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
          "IDBP INTEGER NOT NULL,"+
          "DESC TEXT," +
          "MODEL TEXT);";
        stat.executeUpdate("drop table if exists Boilerplates;");
        stat.executeUpdate(query);
        stat.close();
    }

    public void insertBoilerplate(Boilerplate bp) throws SQLException
    {
        Statement stm = conn.createStatement();
        String query = "INSERT INTO Boilerplates (IDBP, DESC, MODEL) VALUES"+
                        "("+bp.getId()+",\""+bp.getDescription()+"\",\""+bp.getGenericModel()+"\")";
        System.out.println(query);
        stm.executeUpdate(query);
        stm.close();
    }

    public Boilerplate getBoilerplate(int BpId) throws SQLException, Exception
    {
       Statement stat = conn.createStatement();
       ResultSet rs = stat.executeQuery("select * from Boilerplates where IDBP="+BpId+";");
       if(!rs.next()) return null;
       int IDDB = rs.getInt("ID");
       int ID = rs.getInt("IDBP");
       String DESC = rs.getString("DESC");
       String MODEL = rs.getString("MODEL");
       Boilerplate tmp = new Boilerplate(IDDB,ID,DESC,MODEL);
       return tmp;       
    }

    public ArrayList<Boilerplate> getAllBoilerplates() throws SQLException, Exception
    {
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery("select * from Boilerplates;");
        ArrayList<Boilerplate> bps = new ArrayList<Boilerplate>();
        while (rs.next()) {
            int IDDB = rs.getInt("ID");
            int ID = rs.getInt("IDBP");
            String DESC = rs.getString("DESC");
            String MODEL = rs.getString("MODEL");
            Boilerplate tmp = new Boilerplate(IDDB,ID,DESC,MODEL);
            bps.add(tmp);
        }
        rs.close();
        return bps;
    
    }    
}
